package com.beiming.libra.common.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * @author tp Date : 2018/6/14/014 15:08
 */
public class ExcelXlsReader implements HSSFListener {

  private int minColumns = -1;

  private POIFSFileSystem fs;

  private int lastRowNumber;

  private int lastColumnNumber;

  /**
   * Should we output the formula, or the value it has?
   */
  private boolean outputFormulaValues = true;

  /**
   * For parsing Formulas
   */
  private SheetRecordCollectingListener workbookBuildingListener;

  // excel2003工作薄
  private HSSFWorkbook stubWorkbook;

  // Records we pick up as we process
  private SSTRecord sstRecord;

  private FormatTrackingHSSFListener formatListener;

  // 表索引
  private int sheetIndex = -1;

  private BoundSheetRecord[] orderedBSRs;

  @SuppressWarnings("unchecked")
  private ArrayList boundSheetRecords = new ArrayList();

  // For handling formulas with string results
  private int nextRow;

  private int nextColumn;

  private boolean outputNextStringRecord;

  // 当前行
  private int curRow = 0;

  // 存储行记录的容器
  private List<Object> rowlist = new LinkedList<>();

  private List<List<Object>> allValueList = new LinkedList<>();

  @SuppressWarnings("unused")
  private String sheetName;

  /**
   * 遍历excel下所有的sheet
   */
  public void process(String fileName) {
    FileInputStream fileInputStream = null;
    try {
      fileInputStream = new FileInputStream(fileName);
      this.fs = new POIFSFileSystem(fileInputStream);
      MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
      formatListener = new FormatTrackingHSSFListener(listener);
      HSSFEventFactory factory = new HSSFEventFactory();
      HSSFRequest request = new HSSFRequest();
      if (outputFormulaValues) {
        request.addListenerForAllRecords(formatListener);
      } else {
        workbookBuildingListener = new SheetRecordCollectingListener(formatListener);
        request.addListenerForAllRecords(workbookBuildingListener);
      }
      factory.processWorkbookEvents(request, fs);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      close(fileInputStream);
    }
  }

  private void close(FileInputStream fileInputStream) {
    try {
      if (fileInputStream != null) {
        fileInputStream.close();
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
  }

  /**
   * HSSFListener 监听方法，处理 Record
   */
  @SuppressWarnings("unchecked")
  public void processRecord(Record record) {
    int thisRow = -1;
    int thisColumn = -1;
    String thisStr = null;
    String value = null;
    switch (record.getSid()) {
      case BoundSheetRecord.sid:
        boundSheetRecords.add(record);
        break;
      case BOFRecord.sid:
        BOFRecord br = (BOFRecord) record;
        if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
          if (workbookBuildingListener != null && stubWorkbook == null) {
            stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
          }
          sheetIndex++;
          if (orderedBSRs == null) {
            orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
          }
          sheetName = orderedBSRs[sheetIndex].getSheetname();
        }
        break;
      case SSTRecord.sid:
        sstRecord = (SSTRecord) record;
        break;
      case BlankRecord.sid:
        BlankRecord brec = (BlankRecord) record;
        thisRow = brec.getRow();
        thisColumn = brec.getColumn();
        thisStr = "";
        rowlist.add(thisColumn, thisStr);
        break;
      case BoolErrRecord.sid:
        BoolErrRecord berec = (BoolErrRecord) record;
        thisRow = berec.getRow();
        thisColumn = berec.getColumn();
        thisStr = berec.getBooleanValue() + "";
        rowlist.add(thisColumn, thisStr);
        break;
      case FormulaRecord.sid:
        FormulaRecord frec = (FormulaRecord) record;
        thisRow = frec.getRow();
        thisColumn = frec.getColumn();
        if (outputFormulaValues) {
          if (Double.isNaN(frec.getValue())) {
            outputNextStringRecord = true;
            nextRow = frec.getRow();
            nextColumn = frec.getColumn();
          } else {
            thisStr = formatListener.formatNumberDateCell(frec);
          }
        } else {
          thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
        }
        rowlist.add(thisColumn, thisStr);
        break;
      case StringRecord.sid:
        if (outputNextStringRecord) {
          thisRow = nextRow;
          thisColumn = nextColumn;
          outputNextStringRecord = false;
        }
        break;
      case LabelRecord.sid:
        LabelRecord lrec = (LabelRecord) record;
        curRow = thisRow = lrec.getRow();
        thisColumn = lrec.getColumn();
        value = lrec.getValue().trim();
        value = "".equals(value) ? " " : value;
        this.rowlist.add(thisColumn, value);
        break;
      case LabelSSTRecord.sid:
        LabelSSTRecord lsrec = (LabelSSTRecord) record;
        curRow = thisRow = lsrec.getRow();
        thisColumn = lsrec.getColumn();
        if (sstRecord == null) {
          rowlist.add(thisColumn, " ");
        } else {
          value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
          value = "".equals(value) ? " " : value;
          rowlist.add(thisColumn, value);
        }
        break;
      case NumberRecord.sid:
        NumberRecord numrec = (NumberRecord) record;
        curRow = thisRow = numrec.getRow();
        thisColumn = numrec.getColumn();
        value = formatListener.formatNumberDateCell(numrec).trim();
        value = "".equals(value) ? " " : value;
        rowlist.add(thisColumn, value);
        break;
      default:
        break;
    }
    if (thisRow != -1 && thisRow != lastRowNumber) {
      lastColumnNumber = -1;
    }
    processList(thisRow, thisColumn, record);
  }

  private void processList(int thisRow, int thisColumn, Record record) {
    if (record instanceof MissingCellDummyRecord) {
      MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
      curRow = thisRow = mc.getRow();
      thisColumn = mc.getColumn();
      rowlist.add(thisColumn, " ");
    }
    if (thisRow > -1) {
      lastRowNumber = thisRow;
    }
    if (thisColumn > -1) {
      lastColumnNumber = thisColumn;
    }
    if (record instanceof LastCellOfRowDummyRecord) {
      if (minColumns > 0 && lastColumnNumber == -1) {
        lastColumnNumber = 0;
      }
      lastColumnNumber = -1;
      allValueList.add(BeanConvertUtils.deepCopy(rowlist));
      rowlist.clear();
    }
  }

  public List<List<Object>> getAllValueList() {
    return allValueList;
  }

  public void setAllValueList(List<Object> rowlist) {
    this.allValueList.add(rowlist);
  }

  public List<List<Object>> excelReader(String fileName) {
    try {
      this.process(fileName);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return this.getAllValueList();
  }
}
